/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package controle;

import classes.BancoDeDados;
import java.util.ArrayList;
import java.util.List;
import modelo.Compromisso;
import java.sql.ResultSet;
import modelo.Agenda;

/**
 *
 * @author cardodav
 */
public class CompromissoDAO {
    
    public CompromissoDAO(){
    
    }    

    public  List<Compromisso> getCompromissosByCRV(int CRV){
        return this.getCompromissos("V.CRV = " + CRV);
    }
    
    public  List<Compromisso> getCompromissosByDate(int CRV, String data){
        return this.getCompromissos("V.CRV = " + CRV + " and TO_CHAR(C.DATACOMPROMISSO,'YYYY-MM-DD') = '" + data + "'");    
    }
        
    private List<Compromisso> getCompromissos(String criterio){
    
        List<Compromisso> _compromisso = new ArrayList<Compromisso>();
        
        BancoDeDados bd = new BancoDeDados();
        String query = "select C.HORARIO, C.TELEFONECONTATO, C.ANIMALNOME, C.DURACAO, C.DESCRICAO, C.DATACOMPROMISSO, C.CODIGOCOMPROMISSO, C.CODIGOAGENDA ";
        query += " from compromisso c, agenda a, veterinario v ";
        query += " where C.CODIGOAGENDA = A.CODIGOAGENDA and A.CRV = V.CRV ";
        if (criterio != null){
            query += " and " + criterio;
        }
        query += " order by C.CODIGOAGENDA, C.DATACOMPROMISSO, C.HORARIO";
        
        ResultSet rs = bd.consultar(query);
        
        try{
            while (rs.next()){
                Compromisso _c = new Compromisso();
                
                _c.setCodigoCompromisso(rs.getInt("CODIGOCOMPROMISSO"));
                _c.setDataCompromisso(rs.getDate("DATACOMPROMISSO").toString());
                _c.setDescricao(rs.getString("DESCRICAO"));
                _c.setDuracao(rs.getInt("DURACAO"));
                _c.setHorario(rs.getString("HORARIO"));
                _c.setTelefone(rs.getString("TELEFONECONTATO"));
                _c.setAnimalNome(rs.getString("ANIMALNOME"));
                _compromisso.add(_c);
            }   
        }catch(Exception ex){
            System.out.println(ex.getMessage());
        }        
        
        bd.desconectar();
        return _compromisso;        
    }
    
    
    /**
     * deleta o compromisso (o formato da data deve ser YYYY-MM-DD
     * @param CRV
     * @param data 
     */
    public int deletaCompromissos(int CRV, String data){
        
        int ret = -1;
        
        try{
        
        BancoDeDados bd = new BancoDeDados();        
        String query = "DELETE FROM COMPROMISSO WHERE CODIGOCOMPROMISSO IN (";    
        query += "SELECT DISTINCT c.codigocompromisso " +
                "           FROM compromisso c, agenda a, veterinario v " +
                "          WHERE c.codigoagenda = a.codigoagenda " +
                "            AND a.crv = v.crv " +
                "            AND v.crv = " + CRV + " " +
                "            AND TO_CHAR (c.datacompromisso, 'YYYY-MM-DD') = '" + data + "' )";

         int regs = bd.executarDML(query);
         
         if (regs > 0){
             ret = regs;
         }
        
        } catch (Exception ex ){
            System.out.println("Erro ao excluir o compromisso: " + ex.getMessage());
        }        
        
        return ret;        
    }
    
    public int inserirCompromissos(Agenda _a){
    
        int ret = -1;
        
        CompromissoDAO _cDAO = new CompromissoDAO();
        _cDAO.deletaCompromissos(_a.getVeterinario().getCRV(), _a.getCompromissos().get(0).getDataCompromisso() );
        
        for(Compromisso _c : _a.getCompromissos() ){

            int registro = this.inserirCompromisso(_a.getCodigoAgenda(), _c);
            
            if (registro > 0){
                ret = registro;
            }
        }        
        
        return ret;
    
    }   
    
    public int inserirCompromisso(int codigoAgenda, Compromisso _c){
        int ret = -1;
        
        try{
        
            BancoDeDados bd = new BancoDeDados();        
        
            String queryNextCodigo = "Select (max(codigocompromisso) + 1) proximo from compromisso";
            ResultSet rs = bd.consultar(queryNextCodigo);
            
            int iProximoCodigo = 0;
            if (rs.next()){
                iProximoCodigo = rs.getInt("proximo");
            }
        
            String query = "INSERT INTO VETLION.COMPROMISSO ( " +
                        "   DURACAO, DESCRICAO, DATACOMPROMISSO,  " +
                        "   CODIGOCOMPROMISSO, CODIGOAGENDA, ANIMALNOME,  " +
                        "   TELEFONECONTATO, HORARIO)  " +
                        "VALUES ( 30,'" + _c.getDescricao() + "' , TO_DATE('" + _c.getDataCompromisso() + "','YYYY-MM-DD') , " +
                        "   " + iProximoCodigo + "," + codigoAgenda + " ,'" + _c.getAnimalNome() + "' , " +
                        "   '" + _c.getTelefone() + "' ,'" + _c.getHorario() + "' ) ";

            int regs = bd.executarDML(query);

            if (regs > 0){
             ret = regs;
            }
        
        } catch (Exception ex ){
            System.out.println("Erro ao incluir o compromisso: " + ex.getMessage());
        }        
        
        return ret;   
    
    }
    
    
}
